/*
Use this code to get summary statistics and scale ups for the ownership distribution
*/

do "/home2/public/ppp/do/make_globals.do"

//Start with the distributional data file
use "${datadir}/distributional_data", clear

//Handle possible duplicates between non-schedule C and schedule C prioritizing non-schedule C
replace match_type_first = match_type if match_type == 8

//###########
//Simple percentange of ownership match rate

gen total_own_share = o_p20 + o_p40 + o_p60 + o_p80 + o_p90 + o_p95 + o_p99 + o_p100


//Make missing zero
replace total_own_share = 0 if total_own_share == .
//Identify firms that have 100% of ownership distributed
gen hund_pct = total_own_share == 1

//FOLLOWING LINES WERE PARTIALLY REDACTED DUE TO SENSITIVE PARAMETERS
//Get statistics for all loans, loans matched to an EIN and loans matched to an EIN that are not schedule C matches
summarize total_own_share if loannumber_first != ., detail
summarize total_own_share if loannumber_first != ., detail
summarize total_own_share if loannumber_first != . & match_type_first != 8, detail

//FOLLOWING LINES WERE PARTIALLY REDACTED DUE TO SENSITIVE PARAMETERS
//Now summary statistics for matched with non-zero ownership shares
summarize total_own_share if loannumber_first != . & total_own_share > 0, detail
summarize total_own_share if loannumber_first != . & match_type_first != 8 & total_own_share > 0, detail

//Repeate the above but now weighted by amount of first round PPP

replace forgivenessamount_first = 0 if forgivenessamount_first == .
replace forgivenessamount_second = 0 if forgivenessamount_second == .
gen total_ppp = round(forgivenessamount_first + forgivenessamount_second, 0)

//FOLLOWING LINES WERE PARTIALLY REDACTED DUE TO SENSITIVE PARAMETERS
summarize total_own_share [aweight = forgivenessamount_first] if loannumber_first != ., detail
summarize total_own_share [aweight = forgivenessamount_first] if loannumber_first != ., detail
summarize total_own_share [aweight = forgivenessamount_first] if loannumber_first != . & match_type_first != 8, detail

//FOLLOWING LINES WERE PARTIALLY REDACTED DUE TO SENSITIVE PARAMETERS
//Any hit to ownership
summarize total_own_share [aweight = forgivenessamount_first] if loannumber_first != . & total_own_share > 0, detail
summarize total_own_share [aweight = forgivenessamount_first] if loannumber_first != . & match_type_first != 8 & total_own_share > 0, detail


//Now repeat the above for percent that are 100%

//FOLLOWING LINES WERE PARTIALLY REDACTED DUE TO SENSITIVE PARAMETERS
summarize hund_pct if loannumber_first != ., detail
summarize hund_pct if loannumber_first != ., detail
summarize hund_pct if loannumber_first != . & match_type_first != 8, detail

//FOLLOWING LINES WERE PARTIALLY REDACTED DUE TO SENSITIVE PARAMETERS
//Any hit to ownership
summarize hund_pct if loannumber_first != . & total_own_share > 0, detail
summarize hund_pct if loannumber_first != . & match_type_first != 8 & total_own_share > 0, detail

//FOLLOWING LINES WERE PARTIALLY REDACTED DUE TO SENSITIVE PARAMETERS
summarize hund_pct [aweight = forgivenessamount_first] if loannumber_first != ., detail
summarize hund_pct [aweight = forgivenessamount_first] if loannumber_first != . , detail
summarize hund_pct [aweight = forgivenessamount_first] if loannumber_first != . & match_type_first != 8, detail

//FOLLOWING LINES WERE PARTIALLY REDACTED DUE TO SENSITIVE PARAMETERS
//Any hit to ownership
summarize hund_pct [aweight = forgivenessamount_first] if loannumber_first != . & total_own_share > 0, detail
summarize hund_pct [aweight = forgivenessamount_first] if loannumber_first != . & match_type_first != 8 & total_own_share > 0, detail

//clean up the businesstype variables. This will be used in the scale up procedure
gen cond_bus_type = businesstype_first
replace cond_bus_type = "non-profit" if inlist(businesstype_first, "501(c)3 â Non Profit", "501(c)19 â Non Profit Veterans","501(c)3 â Non Profit", "501(c)6 â Non Profit Membership", "Non-Profit Childcare Center", "Non-Profit Organization", "Tribal Concerns", "501(c) â Non Profit except 3,4,6,")
replace cond_bus_type = "sole" if inlist(businesstype_first, "Independent Contractors", "Qualified Joint-Venture (spouses)", "Rollover as Business Start-Ups (ROB", "Self-Employed Individuals", "Single Member LLC", "Sole Proprietorship")
replace cond_bus_type = "partnership" if inlist(businesstype_first, "Cooperative", "Housing Co-op", "Joint Venture", "Limited Liability Partnership", "Partnership")
replace cond_bus_type = "c_corp" if inlist(businesstype_first, "Corporation")
replace cond_bus_type = "s_corp" if inlist(businesstype_first, "Subchapter S Corporation")
replace cond_bus_type = "ownership" if inlist(businesstype_first, "Employee Stock Ownership Plan(ESOP)", "Tenant in Common", "Trust")
replace cond_bus_type = "prof_assoc" if inlist(businesstype_first, "Professional Association")
replace cond_bus_type = "other" if inlist(businesstype_first, "")
replace cond_bus_type = "llc" if inlist(businesstype_first, "Limited  Liability Company(LLC)")

//Dollar amount of loan matched for each firm
gen forgive_match = forgivenessamount_first * total_own_share
//Share of jobs
gen jobs_match = jobsreported_first * total_own_share

//Create the scale up factors by collapsing matched and total dollar and job amount
preserve 
gcollapse (count) loannumber_first (sum) total_own_share forgivenessamount_first forgive_match jobsreported_first jobs_match, by(cond_bus_type)
export excel "/home2/public/ppp/output/sum_stats/scale_up.xlsx", firstrow(variables) sheet("owners", replace)

//Divide to get an inverse match scale-up factor
gen loan_own_wt = loannumber_first / total_own_share
gen dollar_own_wt = forgivenessamount_first / forgive_match 
gen job_own_wt = jobsreported_first / jobs_match

keep cond_bus_type *wt

save "${datadir}/scale_ups/owners", replace

restore

//Produce an excel spreadsheet for a table
preserve 
gcollapse (count) loannumber_first (sum) total_own_share forgivenessamount_first forgive_match jobsreported_first jobs_match if loannumber_first != ., by(ent_type)  //CODE PARTIALLY REDACTED BECAUSE OF SENSITIVE PARAMETERS

export excel "/home2/public/ppp/output/sum_stats/ent_type_owner_match.xlsx", firstrow(variables) replace


restore
